package com.rickpan.repository;

import com.rickpan.entity.UserOnlineStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 用户在线状态数据访问层
 */
@Repository
public interface UserOnlineStatusRepository extends JpaRepository<UserOnlineStatus, Long> {

    /**
     * 根据用户ID查找在线状态
     */
    Optional<UserOnlineStatus> findByUserId(Long userId);

    /**
     * 根据Socket ID查找在线状态
     */
    Optional<UserOnlineStatus> findBySocketId(String socketId);

    /**
     * 查找指定状态的用户
     */
    List<UserOnlineStatus> findByStatus(UserOnlineStatus.Status status);

    /**
     * 查找在线用户
     */
    List<UserOnlineStatus> findByStatusIn(List<UserOnlineStatus.Status> statuses);

    /**
     * 统计各状态的用户数量
     */
    @Query("SELECT uos.status, COUNT(uos.status) FROM UserOnlineStatus uos GROUP BY uos.status")
    List<Object[]> getStatusStatistics();

    /**
     * 统计在线用户数量
     */
    long countByStatus(UserOnlineStatus.Status status);

    /**
     * 查找指定用户列表的在线状态
     */
    @Query("SELECT uos FROM UserOnlineStatus uos WHERE uos.userId IN :userIds")
    List<UserOnlineStatus> findByUserIds(@Param("userIds") List<Long> userIds);

    /**
     * 批量更新用户状态为离线
     */
    @Modifying
    @Transactional
    @Query("UPDATE UserOnlineStatus uos SET uos.status = 'OFFLINE', uos.socketId = null, uos.lastSeen = :now " +
           "WHERE uos.userId IN :userIds")
    int batchSetOffline(@Param("userIds") List<Long> userIds, @Param("now") LocalDateTime now);

    /**
     * 清理过期的在线状态（超过指定时间未更新的设为离线）
     */
    @Modifying
    @Transactional
    @Query("UPDATE UserOnlineStatus uos SET uos.status = 'OFFLINE', uos.socketId = null " +
           "WHERE uos.status != 'OFFLINE' AND uos.updatedAt < :expireTime")
    int cleanupExpiredOnlineStatus(@Param("expireTime") LocalDateTime expireTime);

    /**
     * 根据Socket ID设置用户离线
     */
    @Modifying
    @Transactional
    @Query("UPDATE UserOnlineStatus uos SET uos.status = 'OFFLINE', uos.socketId = null, uos.lastSeen = :now " +
           "WHERE uos.socketId = :socketId")
    int setOfflineBySocketId(@Param("socketId") String socketId, @Param("now") LocalDateTime now);

    /**
     * 更新用户在线状态
     */
    @Modifying
    @Transactional
    @Query("UPDATE UserOnlineStatus uos SET uos.status = :status, uos.socketId = :socketId, " +
           "uos.deviceInfo = :deviceInfo, uos.ipAddress = :ipAddress, uos.lastSeen = :now " +
           "WHERE uos.userId = :userId")
    int updateOnlineStatus(@Param("userId") Long userId, 
                          @Param("status") UserOnlineStatus.Status status,
                          @Param("socketId") String socketId,
                          @Param("deviceInfo") String deviceInfo,
                          @Param("ipAddress") String ipAddress,
                          @Param("now") LocalDateTime now);

    /**
     * 更新最后在线时间
     */
    @Modifying
    @Transactional
    @Query("UPDATE UserOnlineStatus uos SET uos.lastSeen = :now WHERE uos.userId = :userId")
    int updateLastSeen(@Param("userId") Long userId, @Param("now") LocalDateTime now);

    /**
     * 删除用户的在线状态记录
     */
    void deleteByUserId(Long userId);

    /**
     * 检查用户是否在线
     */
    @Query("SELECT CASE WHEN uos.status = 'ONLINE' THEN true ELSE false END " +
           "FROM UserOnlineStatus uos WHERE uos.userId = :userId")
    Boolean isUserOnline(@Param("userId") Long userId);

    /**
     * 查找长时间未活动的在线用户（可能需要设为离线）
     */
    @Query("SELECT uos FROM UserOnlineStatus uos WHERE " +
           "uos.status = 'ONLINE' AND uos.lastSeen < :inactiveTime")
    List<UserOnlineStatus> findInactiveOnlineUsers(@Param("inactiveTime") LocalDateTime inactiveTime);

    /**
     * 获取用户的好友在线状态
     */
    @Query("SELECT uos FROM UserOnlineStatus uos " +
           "WHERE uos.userId IN (" +
           "SELECT fr.friendId FROM FriendRelationship fr " +
           "WHERE fr.userId = :userId AND fr.status = 'ACTIVE')")
    List<UserOnlineStatus> getFriendsOnlineStatus(@Param("userId") Long userId);

    /**
     * 查找指定IP地址的在线用户
     */
    List<UserOnlineStatus> findByIpAddressAndStatus(String ipAddress, UserOnlineStatus.Status status);

    /**
     * 获取在线用户的设备信息统计
     */
    @Query("SELECT uos.deviceInfo, COUNT(uos.deviceInfo) FROM UserOnlineStatus uos " +
           "WHERE uos.status = 'ONLINE' AND uos.deviceInfo IS NOT NULL " +
           "GROUP BY uos.deviceInfo")
    List<Object[]> getOnlineDeviceStats();

    /**
     * 检查Socket ID是否已被使用
     */
    boolean existsBySocketId(String socketId);

    /**
     * 检查用户是否存在在线状态记录
     */
    boolean existsByUserId(Long userId);
}
